---
title: "Libraries dashboard (`r format(Sys.time(), '%d/%m/%y')`)"
output: 
  flexdashboard::flex_dashboard:
    orientation: columns
    vertical_layout: fill
    source_code: embed
---

```{r setup, include=FALSE}
library(flexdashboard)
library(kableExtra); library(DT)
library(writexl)

source("LibraryFunctions.R")

# Define a theme for the plots in the dashboard 
theme_set(theme_minimal())

# Used to create Excel workbook containing output
output_list <- list()
```

```{r global, include=FALSE}
# Load data in 'global' chunk so it can be shared across the dashboard

# Libraries 
libraries <- read_xlsx(libraries_data_in_xlsx, "library") %>% 
  mutate(library_id = as.integer(library_id),
         cluster = as.integer(cluster)) %>% 
  arrange(cluster, library)

# Hub libraries
hubs <- libraries %>% 
  filter(library_type == "hub")

# Open hours 
opening <- read_xlsx(libraries_data_in_xlsx, "opening") %>% 
  mutate(library_id = as.integer(library_id),
         day = factor(day, levels = days_of_work_week),
         start = hms::as_hms(start),
         end = hms::as_hms(end),
         hours_open = hms::as_hms((end - start))) %>% 
  right_join(select(hubs, library_id, cluster),
             by = c("library_id" = "library_id")) %>% 
  relocate(cluster, .after = library) %>% 
  arrange(cluster, library)

# Staff
staff <- read_xlsx(libraries_data_in_xlsx, "staff") %>%
  mutate(post_id = as.integer(post_id),
         cluster = as.integer(cluster),
         library_id = as.integer(library_id),
         name = str_trim(str_c(first_name, " ", str_replace_na(last_name, ""))),
         contract_hours = hms(hours = contract_hours)) %>%
  select(post_id, post_number, name, first_name, last_name,
         contract_hours, library_id, location, cluster)

# Staff variables  
staff_cost <- get_var("staff_cost")
fte_hours <- get_var("fte_hours")
weeks_in_rota_period <- 4

# Schedules
schedule <- read_xlsx(libraries_data_in_xlsx, "schedule") %>% 
  mutate(post_id = as.integer(post_id),
         library_id = as.integer(library_id),
         week_no = as.integer(week_no),
         day = factor(day, levels = days_of_work_week),
         start = hms::as_hms(start),
         end = hms::as_hms(end),
         lunch = hms::as_hms(lunch),
         daily_hours = hms::as_hms((end - start - lunch))) %>% 
  filter(schedule_type == "current") %>% 
  right_join(libraries, by = c("library_id" = "library_id")) %>% 
  left_join(select(staff, post_id, name), 
            by = c("post_id" = "post_id")) %>% 
  filter(name != "VACANT") %>%
  select(cluster, library_id, library, week_no, 
         day, start, end, lunch, daily_hours, name, post_id) %>% 
  arrange(cluster, library, week_no, day, name)

# Activity type
activity_type <- read_xlsx(libraries_data_in_xlsx, "activity_type") %>% 
  mutate(activity_type_id = as.integer(activity_type_id))

# Activities
activities <- read_xlsx(libraries_data_in_xlsx, "activities") %>% 
  mutate(activity_type_id = as.integer(activity_type_id),
         opening_id = as.integer(opening_id)) %>% 
  left_join(activity_type, by = c("activity_type_id" = "activity_type_id")) %>% 
  left_join(opening, by = c("opening_id" = "opening_id")) %>% 
  relocate(starts_with("activity"), .after = last_col())
```

# Opening hours

## Column {.tabset .tabset-fade data-width="300"}

### Description

```{r library-desc}
# Number of hub libraries
n_hubs <- nrow(hubs)

# Number of hub libraries in each cluster
n_hubs_cluster1 <- nrow(filter(hubs, cluster == 1))
n_hubs_cluster2 <- nrow(filter(hubs, cluster == 2))
n_hubs_cluster3 <- nrow(filter(hubs, cluster == 3))

# Number of hours open a week for each library
opening_summary <- opening %>%
  count(library_id, library, cluster, 
        wt = hours_open/dhours(1), name = "hours_open") %>% 
  arrange(cluster, library)

#Average hours open a week
n_hours_open <- as.integer(tally(opening_summary, hours_open)/n_hubs)
```

There are `r n_hubs` hub libraries grouped into `r n_clusters` clusters. There are `r n_hubs_cluster1`, `r n_hubs_cluster2` and `r n_hubs_cluster3` libraries in clusters 1, 2 and 3 respectively.

Every hub library is open for `r n_hours_open` hours a week. No libraries open on Sunday. All libraries are open on Monday, Friday and Saturday. 6 libraries close on Thursday, 4 on Wednesday and 1 on Tuesday.

*Note: We're waiting on data for the central library.*

### Hours a week

```{r library-total-hours}
# Present in a nice table
kable(select(opening_summary, cluster, library, hours_open)) %>% 
  kable_styling("striped", position = "left", full_width = FALSE)
```

### Shift summary

```{r shifts-summary}
shifts_cluster1 <- opening %>%
  filter(cluster == 1) %>% 
  count(day, name = "shifts_cluster1")

shifts_cluster2 <- opening %>%
  filter(cluster == 2) %>% 
  count(day, name = "shifts_cluster2")

shifts_cluster3 <- opening %>%
  filter(cluster == 3) %>% 
  count(day, name = "shifts_cluster3")

shifts_summary <- shifts_cluster1 %>% 
  left_join(shifts_cluster2, by = "day") %>% 
  left_join(shifts_cluster3, by = "day")

# Present in a nice table
kable(shifts_summary) %>% 
  kable_styling("striped", position = "left", full_width = FALSE)
```

## Column {.tabset .tabset-fade}

### Library opening

```{r plot-opening}
opening %>%
  arrange(library) %>%
  mutate(type = "opening hours") %>%
  rename(activity = library) %>%
  library_hours_plot("")
```

### List of library opening

```{r list-opening, warning = FALSE}
# Don't need to show the ID fields in the dashboard
opening_list <- opening %>%
  select(-ends_with("id"))

# Present in a nice table
datatable(opening_list, rownames = FALSE, filter = "bottom",
          options = list(scrollX = TRUE))
```

# Staff schedules

## Column {.tabset .tabset-fade data-width="300"}

### Description

```{r schedules-desc, include=FALSE, warning = FALSE}
# Scheduled staff hours by library
hours_by_library <- schedule %>% 
  group_by(library_id, library) %>% 
  summarise(staff_hours = hms::as_hms(sum(daily_hours)/weeks_in_rota_period)) %>% 
  ungroup() %>% 
  mutate(fte = round(staff_hours/dhours(fte_hours), 1)) %>% 
  filter(!is.na(staff_hours)) %>% 
  arrange(staff_hours)

# Number of FTE hub library staff scheduled
n_hub_scheduled_fte <- round((sum(hours_by_library$staff_hours)/dhours(1))/fte_hours, 1)

# Average number of FTE staff scheduled per hub library
n_ave_hub_scheduled_fte <-  round(n_hub_scheduled_fte/n_hubs, 1)
```

The average FTE scheduled staff hours per hub library is `r n_ave_hub_scheduled_fte`. 

Stocksbridge and Parson Cross libraries have the least FTE (Full Time Equivalent) staff scheduled with an average of 2.8, whilst Firth Park has the most with 4.9.

### By library

```{r hours-by-library, warning = FALSE}
# Present in a nice table
select(hours_by_library, library, staff_hours, fte) %>% 
  kable() %>% 
  kable_styling("striped", position = "left", full_width = FALSE)
```

*Note: Hours are an average over 4 weeks.*

## Column {.tabset .tabset-fade}

### Staff hours by library & week

```{r plot-hours-by-library-week, warning = FALSE}
# Scheduled staff hours by library & week
hours_by_library_week <- schedule %>% 
  rename(week = week_no) %>% 
  group_by(library, week) %>% 
  summarise(staff_hours = hms::as_hms(sum(daily_hours))) %>% 
  mutate(fte = round(staff_hours/dhours(fte_hours), 1),
         week = factor(week)) %>% 
  filter(!is.na(staff_hours)) %>% 
  arrange(library, week)

# Plot of staff hours by library and week
ggplot(data=hours_by_library_week, 
       aes(x=library, y=fte, fill=week)) +
    geom_bar(stat="identity", position=position_dodge()) +
    theme(axis.text.x = element_text(angle = 315, hjust=0.1),
          plot.margin = unit(c(5.5,12,5.5,5.5), "pt"),
          legend.position = c(0.8, 0.8),
          legend.key.size = unit(0.4, 'cm'))
```

# Activities

## Column {.tabset .tabset-fade data-width="300"}

### Description

The differences in FTE scheduled hours between libraries is not completely explained by the different number of activities at different libraries.

*Note: Activities data only available for 4 out of 5 libraries in cluster 1. None available for clusters 2 and 3.*

### By library

```{r activities-by-library, warning = FALSE}
# Number of activities per library (per week)  
activities_by_library <- activities %>% 
  count(library_id, library, name = "activities_per_week") %>% 
  mutate(activities_per_week = ifelse(is.na(library), 0, activities_per_week)) %>% 
  select(-library) %>% 
  left_join(libraries, by = c("library_id" = "library_id")) %>% 
  arrange(desc(activities_per_week), library)

# Add average scheduled FTE column
activities_by_library %<>% 
  left_join(select(hours_by_library, -library), 
            by = c("library_id" = "library_id"))

# Present in a nice table
select(activities_by_library, library, activities_per_week, fte) %>% 
  kable() %>% 
  kable_styling("striped", position = "left", full_width = FALSE)
```

## Column {.tabset .tabset-fade}

### Activities & scheduled staff hours by library

```{r plot-activities-by-library, warning = FALSE}
# Define the two colours we're using to help compare activities & FTE hours
activities_colour = "blue4"
fte_colour = "olivedrab4"

# Order the libraries how we want them to be plotted
activities_by_library %<>%
  arrange(-activities_per_week, -fte) %>%
  mutate(library = factor(library, levels=library)) 

# Plot of activities and staff hours by library
ggplot(data=activities_by_library,
       aes(x=library)) +
  geom_point(aes(y=activities_per_week), colour=activities_colour, size=3) +
  geom_point(aes(y=fte), colour=fte_colour, size=3) +
  scale_x_discrete(name = "library") +
  scale_y_continuous(name = "activities per week",
                     sec.axis = sec_axis(trans=~.*1, 
                                         name="FTE scheduled hours")) +
  expand_limits(y = 0) +
  theme(axis.title.y = element_text(colour = activities_colour),
        axis.line.y = element_line(color = activities_colour),
        axis.ticks.y = element_line(color = activities_colour),
        axis.title.y.right = element_text(colour = fte_colour),
        axis.line.y.right = element_line(color = fte_colour),
        axis.ticks.y.right = element_line(color = fte_colour),
        axis.text.x = element_text(angle = 315, hjust=0.1),
        plot.margin = unit(c(5.5,12,5.5,5.5), "pt"))
```

### List of activities

```{r activities-list, warning = FALSE}
# Don't need to include ID fields in the dashboard
activities_list <- activities %>% 
  select(-ends_with("_id"))

# Present in a nice table
datatable(activities_list, rownames = FALSE, filter = "bottom",
          options = list(scrollX = TRUE))
```